<?php
 /**
  * 按岗位导入岗位组织结构图
  * **/
apf_require_class('Oa_Job_Attendance_Abstract');
class Oa_Job_Attendance_OaSynergyPosition extends Oa_Job_Attendance_Abstract {

    /** 数据库读 **/
    private $readdb = 'attendance_slave';

    /** 数据库写 **/
    private $writedb = 'attendance_master';


    public function run() {
        $start = microtime(true);
        $this->log("开始执行啦！");
        $this->db_connect();

        $this->init_data();
        $this->import_position_data();


        $end = microtime(true) - $start;
        $this->log("共耗时:".$end);
        $this->log("job执行结束！");
    }

    private function import_position_data() {
        #1.查出所有员工 的职位信息
        //$s_sql = "select EM_ID,Code,PositionName,PositionID from v_report_employeedetailinfo where (Code = 'SHE2314' or Code = 'shf4085' or code = 'SHA008' or code = 'SHB045' or code = 'SHA001')";
        $s_sql = "select EM_ID,Code,PositionName,PositionID from v_report_employeedetailinfo";
        $result = mssql_query($s_sql);
        $code_info = $position_info = array();

        while($row = mssql_fetch_assoc($result)) {
            if(empty($row['EM_ID'])) {continue;}
            $position_info[mssql_guid_string($row['EM_ID'])]['name'] = $this->set_utf8($row['PositionName']);
            $position_info[mssql_guid_string($row['EM_ID'])]['id'] = mssql_guid_string($row['PositionID']);
            $code_info[] = "'".$row['Code']."'";
        }
        if(empty($code_info)) {return array();}

        #2.获得所有用户em_id => user_id
        $user_info = $this->get_user_ids();

        #3.查出所有人的上级用户信息
        $code_info = implode(",",$code_info);
        $s_sql = "select he.EM_ID,hvre.Name,hvre.EM_ID as pem_id,he.Name as cname,hvre.Code as pcode,he.Code as code,hvre.Status as pStatus,hvre.positionid as parent_position_id FROM HR_v_Report_EmployeeAllDetailInfo hvre
                 LEFT JOIN ORG_Position op ON op.ParentID = hvre.PositionID
                 LEFT JOIN  v_Report_Workinfo vrw  ON op.ID = vrw.PositionID
                 LEFT JOIN HM_Employees he ON he.EM_ID = vrw.EM_ID WHERE he.Code in ({$code_info}) and he.Status = 1";


        $relation_info = mssql_query($s_sql);
        while($row = mssql_fetch_assoc($relation_info)) {
            $row['pem_id'] = mssql_guid_string($row['pem_id']);
            $row['EM_ID'] = mssql_guid_string($row['EM_ID']);
            $row['user_id'] = $user_info[$row['EM_ID']];
            $row['parent_user_id'] = $user_info[$row['pem_id']];
            $row['pStatus'] = $this->set_utf8($row['pStatus']);
            $row['Name'] = $this->set_utf8($row['Name']);
            $row['cname'] = $this->set_utf8($row['cname']);
            $row['positionName'] = $position_info[$row['EM_ID']]['name'];
            $row['positionId'] = $position_info[$row['EM_ID']]['id'];
            //$row['parent_position_id'] = $position_info[$row['pem_id']]['id'];
            $row['parent_position_id'] = mssql_guid_string($row['parent_position_id']);
            $row['status'] = 1;
            if(!empty($row['user_id']) && $row['pStatus'] != '离职') {
                if(empty($row['parent_user_id']) && $row['code'] != 'SHA001') {
                    $this->log('用户数据错误！请检查 start');
                    print_r($row);
                    $this->log('用户数据错误！请检查 end');
                    sleep(1);
                    continue;
                }
                $row['parent_user_id'] = empty($row['parent_user_id']) ? 0:$row['parent_user_id'];
                $i_sql = "insert into user_position_info (user_id,user_name,position_id,position_name,parent_user_id,parent_position_id,status) values ({$row['user_id']},'{$row['cname']}','{$row['positionId']}','{$row['positionName']}','{$row['parent_user_id']}','{$row['parent_position_id']}',{$row['status']})";
                $this->execute($i_sql,array(),$this->writedb);
            }else {
                print_r($row);
            }
        }

        #4.查出兼职信息
        $s_sql = "select hep.em_id as pem_id,hep.positionid as Pid,op.name as pname,vrw.positionID as Cid,vrw.positionName,vrw.employeename as user_name from hm_em_parttime hep
                  left join v_report_workinfo vrw on hep.em_id = vrw.em_id
                  left join org_position as op on op.id = hep.positionid";
        $query = mssql_query($s_sql);
        $other_relation_pid_info = $other_relation_info = array();
        $parent_other_relation_info = array();
        while($row = mssql_fetch_assoc($query)) {
            $other_relation_pid_info[] = mssql_guid_string($row['Pid']);
            $other_relation_info[mssql_guid_string($row['Pid'])]['emid'] = mssql_guid_string($row['pem_id']);
            $other_relation_info[mssql_guid_string($row['Pid'])]['positionid'] = mssql_guid_string($row['Pid']);
            $other_relation_info[mssql_guid_string($row['Pid'])]['positionname'] = $this->set_utf8($row['pname']);
            $other_relation_info[mssql_guid_string($row['Pid'])]['user_id'] = $user_info[mssql_guid_string($row['pem_id'])];
            $other_relation_info[mssql_guid_string($row['Pid'])]['user_name'] = $this->set_utf8($row['user_name']);
        }

        #4.1循环取出上一级,先排除一次
        $parent_other_relation_info = $this->get_parent_relation_info($other_relation_pid_info);

        #4.2取出上级信息不存在的p_position_id
        $filter_position_info = array();
        if(!empty($parent_other_relation_info)) {
            foreach($parent_other_relation_info as $id => $v) {

                $v['p_user_name'] = trim($v['p_user_name']);
                if(empty($v['p_user_name'])) {
                    $rs = $this->_get_parent_relation_info($v['p_position_id']);
                    if(empty($user_info[$rs['em_id']])) {
                        if($rs['p_position_id'] != $root_position_id) {
                            $root_position_id = APF::get_instance()->get_config("root_position_id");
                            $root_em_id = APF::get_instance()->get_config("root_em_id");
                            $other_relation_info[$id]['parent_user_id'] = $user_info[$root_em_id];
                            $other_relation_info[$id]['parent_position_id'] = $root_position_id;
                        }
                    }else {
                        $other_relation_info[$id]['parent_user_id'] = $user_info[$rs['em_id']];
                        $other_relation_info[$id]['parent_position_id'] = $rs['p_position_id'];
                    }

                }else {
                    if(isset($other_relation_info[$id])) {
                        $other_relation_info[$id]['parent_user_id'] = $user_info[$v['p_emid']];
                        $other_relation_info[$id]['parent_position_id'] = $v['p_position_id'];
                    }
                }
            }
        }

        if(!empty($other_relation_info)) {
            foreach($other_relation_info as $row) {
                $row['parent_user_id'] = empty($row['parent_user_id']) ? 0:$row['parent_user_id'];
                $i_sql = "insert into user_position_info (user_id,user_name,position_id,position_name,parent_user_id,parent_position_id,status) values ({$row['user_id']},'{$row['user_name']}','{$row['positionid']}','{$row['positionname']}','{$row['parent_user_id']}','{$row['parent_position_id']}','2')";
                $this->execute($i_sql,array(),$this->writedb);
            }
        }
    }

    private function _get_parent_relation_info($p_position_id,&$i = 0) {
        if($i > 6) {return array();}
        $result = array();
        $s_sql = "select vrw.em_id,vrw.employeename as user_name,op.id,op.parentid from org_position as op
                  left join v_report_workinfo as vrw on vrw.positionid = op.parentid
                  right JOIN HM_Employees he ON he.EM_ID = vrw.EM_ID WHERE he.Status = 1
                  and op.id = '{$p_position_id}'";

        $query = mssql_query($s_sql);

        while($row = mssql_fetch_assoc($query)) {
            $result['em_id'] = trim(mssql_guid_string($row['em_id']));
            $result['positionid'] = trim(mssql_guid_string($row['positionid']));
            $result['parentid'] = trim(mssql_guid_string($row['parentid']));
            $result['p_position_id'] = trim(mssql_guid_string($row['id']));
            break;
        }
        $i++;
        if(empty($result['em_id']) && !empty($result['parentid'])) {
            $result = $this->_get_parent_relation_info($result['parentid'],$i);
        }

        return $result;
    }

    private function get_parent_relation_info($relation_info) {
        $result = array();
        $in_list = implode("','",$relation_info);
        $in_list = "'".$in_list."'";
        $s_sql = "select op.id,op.ParentID,vrw.EmployeeName,vrw.em_id from ORG_Position as op
                  left join v_report_workinfo as vrw on vrw.positionid = op.parentID where op.id in ({$in_list})";

        $relation_info = mssql_query($s_sql);
        while($row = mssql_fetch_assoc($relation_info)) {
            $result[mssql_guid_string($row['id'])]['p_position_id'] = mssql_guid_string($row['ParentID']);
            $result[mssql_guid_string($row['id'])]['p_emid'] = mssql_guid_string($row['em_id']);
            $result[mssql_guid_string($row['id'])]['p_user_name'] = $this->set_utf8($row['EmployeeName']);
        }
        return $result;
    }

    private function get_user_ids() {
        $result = array();
        $s_sql = "select user_id,kd_em_id from user_info";
        $rs = $this->get_db_rows($s_sql, $this->readdb);
        if(empty($rs)) {return $result;}

        foreach($rs as $v) {
            $result[$v['kd_em_id']] = $v['user_id'];
        }
        return $result;
    }

    public function set_utf8($str) {
        $origin = mb_detect_encoding($str,array('CP936','UTF-8','ASCII','GBK','GB2312'));
        return mb_convert_encoding($str,'UTF-8',$origin);
    }

    private function init_data() {
        $d_sql = "delete from user_position_info";
        $this->execute($d_sql,array(),$this->writedb);
    }



    public function db_connect() {
        $dbhost = "192.168.1.60";
        $dbusname = "sa";
        $dbpasswd = "sa";
        $dbname = "AIS20100415101750";
        $conn = mssql_connect(
            $dbhost,
            $dbusname,
            $dbpasswd
        );
        mssql_select_db($dbname,$conn);
    }
}
